package com.tisson.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.tisson.database.FormatUtil;
import com.tisson.database.StatementUtil;
import com.tisson.pojo.AcDepartment;

public class AcDepartmentDAO {
	private String query;
	private ResultSet rs;
	private Statement stmt;

	public AcDepartment findById(String id) throws SQLException {
		stmt = StatementUtil.getStatement();
		query = "select * from ac_department where DepartmentId = '" + id + "'";
		rs = stmt.executeQuery(query);
		AcDepartment department = new AcDepartment();
		while (rs.next()) {
			department.setDepartmentId(FormatUtil.ConvertUuid(rs
					.getBytes("DepartmentId")));
			department.setDepName((String) rs.getObject("DepName"));
			department.setDepAdmin((String) rs.getObject("DepAdmin"));
			department.setDepAddress((String) rs.getObject("DepAddress"));
			department.setDepPhone((String) rs.getObject("DepPhone"));
		}
		rs.close();
		stmt.close();
		return department;
	}

	public List combineQuery(String name, String address) throws SQLException {
		if ((address == null || address.equals(""))
				&& (name == null || name.equals(""))) {
			query = "select * from ac_department";
		} else if (address == null || address.equals("")) {
			query = "select * from ac_department where DepName = '" + name
					+ "'";
		} else if (name == null || name.equals("")) {
			query = "select * from ac_department where DepAddress = '"
				+ address + "'";
		} else {
			query = "select * from ac_department where DepName = '" + name
					+ "' and DepAddress = '" + address + "'";
		}
		return searchAll();
	}

	public List findAll() throws SQLException {
		query = "select * from ac_department";
		return searchAll();
	}

	private List searchAll() throws SQLException {
		stmt = StatementUtil.getStatement();
		rs = stmt.executeQuery(query);
		List list = new ArrayList();
		while (rs.next()) {
			AcDepartment department = new AcDepartment();
			department.setDepartmentId(FormatUtil.ConvertUuid(rs
					.getBytes("DepartmentId")));
			department.setDepName((String) rs.getObject("DepName"));
			department.setDepAdmin((String) rs.getObject("DepAdmin"));
			department.setDepAddress((String) rs.getObject("DepAddress"));
			department.setDepPhone((String) rs.getObject("DepPhone"));
			list.add(department);
		}
		return list;
	}

	public List search(AcDepartment department, List logic) throws SQLException {
		List list = new ArrayList();
		stmt = StatementUtil.getStatement();
		query = "SELECT * FROM ac_department WHERE ";
		if (department.getDepartmentId() != null) {
			query += "DepartmentId LIKE '%" + department.getDepartmentId()
					+ "%' ";
		}
		query += logic.get(0) + " ";
		if (department.getDepartmentId() != null) {
			query += "DepName LIKE '%" + department.getDepName() + "%' ";
		}
		query += logic.get(1) + " ";
		if (department.getDepartmentId() != null) {
			query += "DepAdmin LIKE '%" + department.getDepAdmin() + "%' ";
		}
		query += logic.get(2) + " ";
		if (department.getDepartmentId() != null) {
			query += "DepAddress LIKE '%" + department.getDepAddress() + "%' ";
		}
		query += logic.get(3) + " ";
		if (department.getDepartmentId() != null) {
			query += "DepPhone LIKE '%" + department.getDepPhone() + "%' ";
		}
		rs = stmt.executeQuery(query);
		while (rs.next()) {
			AcDepartment dept = new AcDepartment();
			dept.setDepartmentId(FormatUtil.ConvertUuid(rs
					.getBytes("DepartmentId")));
			dept.setDepName(rs.getString("DepName"));
			dept.setDepAdmin(rs.getString("DepAdmin"));
			dept.setDepAddress(rs.getString("DepAddress"));
			dept.setDepPhone(rs.getString("DepPhone"));
			list.add(dept);
		}
		rs.close();
		stmt.close();
		return list;
	}

	public void save(AcDepartment department) throws SQLException {
		stmt = StatementUtil.getStatement();
		query = "insert into ac_department (DepName, DepAdmin, "
				+ "DepAddress, DepPhone)values('" + department.getDepName()
				+ "','" + department.getDepAdmin() + "','"
				+ department.getDepAddress() + "','" + department.getDepPhone()
				+ "')";
		stmt.executeUpdate(query);
		stmt.close();
	}

	public void update(AcDepartment department) throws SQLException {
		stmt = StatementUtil.getStatement();
		query = "update ac_department set " + "DepName = '"
				+ department.getDepName() + "',DepAdmin = '"
				+ department.getDepAdmin() + "',DepAddress = '"
				+ department.getDepAddress() + "',DepPhone = '"
				+ department.getDepPhone() + "'" + " where DepartmentId = '"
				+ department.getDepartmentId() + "'";
		stmt.executeUpdate(query);
		stmt.close();
	}

	public void delete(AcDepartment department) throws SQLException {
		stmt = StatementUtil.getStatement();
		query = "delete from ac_department where DepartmentId = '"
				+ department.getDepartmentId() + "'";
		stmt.executeUpdate(query);
		stmt.close();
	}
}
